﻿'Description: 工序数据维护后台方法
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Zhangrj 
'create Date:20220516
'Rewriter:
'Rewrite Date:
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient
Public Class WorkFlowStepMaintBusiness
    ''' <summary>
    ''' 获取工艺工序
    ''' </summary>
    ''' <param name="strWorkflowID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkflowStepData(ByVal strWorkflowID As String) As System.Data.DataTable
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT wf.workflowtype,wf.ly2modle_node, WFS.WORKFLOWSTEPNAME,WFS.NOTES,round(STD.DURATIONPERUNIT,3) DURATIONPERUNIT,WFS.WORKFLOWSTEPID,WFS.WORKCENTERID,WC.WORKCENTERNAME,")
        sb.AppendLine("WFS.TEAMID,T.TEAMNAME,round(WFS.MANHOURS,3) MANHOURS,")
        'sb.AppendLine("sio.stepno||'-'||sio.description stepinfodesc,sio.durationperunit stepinfodura,sio.durationperunit2,sio.stepinfoid,sib.stepinfoname,sio.notes stepinfonotes,sib.revofrcdid,")
        sb.AppendLine("em.fullname CheckTechnique,em.employeeid CheckTechniqueID,wfs.Commoditytime")
        sb.AppendLine(",s.lastcheck,s.controlcode,s.specid,s.opdesc,s.specno,wf.factoryid wffactoryid,s.factoryid sfactoryid,round(STD.setuptime,3) setuptime")
        sb.AppendLine(",WFS.Preparecycle,WFS.Makecycle,WFS.Transfercycle,WFS.Shipcycle,WFS.Commicycle,WFS.makeempcounts,WFS.ChargingQty,nvl(WFS.multimachineparallel,0) multimachineparallel,DECODE(WFS.multimachineparallel,1,'是','否') multimachineparallelname ")
        sb.AppendLine("FROM WORKFLOWSTEP WFS")
        sb.AppendLine("LEFT JOIN Workflow wf ON wfs.workflowid = wf.workflowid")
        sb.AppendLine("LEFT JOIN specbase sb_ ON sb_.specbaseid = wfs.specbaseid")
        sb.AppendLine("LEFT JOIN spec s ON s.specid = NVL(sb_.revofrcdid,wfs.specid)")
        sb.AppendLine("LEFT JOIN employee em ON em.employeeid = wf.zgemployeeid")
        sb.AppendLine("LEFT JOIN WORKCENTER WC ON WC.WORKCENTERID = WFS.WORKCENTERID")
        sb.AppendLine("LEFT JOIN TEAM T ON T.TEAMID = WFS.TEAMID")
        sb.AppendLine("LEFT JOIN STEPSCHEDULINGDETAIL STD ON STD.STEPID = WFS.WORKFLOWSTEPID")
        'sb.AppendLine("LEFT JOIN stepinfo sio ON sio.workflowstepid = wfs.workflowstepid")
        'sb.AppendLine("LEFT JOIN stepinfobase sib ON sio.stepinfoid=sib.revofrcdid ")
        sb.AppendFormat(" WHERE WFS.WORKFLOWID = '{0}' AND WFS.SEQUENCE IS NOT NULL", strWorkflowID)
        sb.AppendLine(" ORDER BY WFS.SEQUENCE")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function
    ''' <summary>
    ''' 获取工序设备信息
    ''' </summary>
    ''' <param name="strStepIds"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepResourceDatas(ByVal strStepIds As String) As DataTable
        Dim strSql As New StringBuilder
        strSql.AppendLine("SELECT sr.*,s.opdesc,s.specno,r.resourcename,r.description FROM SpecResourceInfo sr ")
        strSql.AppendLine("LEFT JOIN resourcedef r ON r.resourceid = sr.resourceid ")
        strSql.AppendLine("LEFT JOIN spec s ON s.specid = sr.specid ")
        strSql.AppendFormat("WHERE sr.specid IN ({0})", strStepIds)
        strSql.AppendLine("order by s.specno")
        Return DatabaseHelper.Query_ReturnDatTable(strSql.ToString)
    End Function


    ''' <summary>
    ''' 添加工序设备数据
    ''' </summary>
    ''' <param name="dtResource"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CreateStepResourceData(ByVal dtResource As DataTable) As uMESProcessResult
        Dim result As New uMESProcessResult
        Try
            Dim sqlList As New ArrayList
            Dim sql As String = String.Empty
            For Each dr As DataRow In dtResource.Rows
                sql = String.Format(<string>                     
                                    SELECT 1 FROM resourcedef r 
                                    WHERE r.resourceid = '{0}' AND r.factoryid = 
                                    (SELECT s.factoryid 
                                    FROM spec s WHERE s.specid = '{1}')
                                    </string>.Value, dr("RESOURCEID").ToString, dr("SPECID").ToString)
                If DatabaseHelper.GetSingle(sql) Is Nothing Then '如果工序设备分厂不一致，则不添加
                    Continue For
                End If
                sql = String.Format("select 1 from SpecResourceInfo sri where sri.specid='{0}' and sri.Resourceid='{1}'", dr("SPECID").ToString, dr("RESOURCEID").ToString)
                If DatabaseHelper.GetSingle(sql) Is Nothing Then '没有该工序设备，才插入
                    sql = "insert into SpecResourceInfo (specresourceinfoid,Resourceid,specid) values (sys_guid(),'{0}','{1}') "
                    sql = String.Format(sql, dr("RESOURCEID").ToString, dr("SPECID").ToString)
                    sqlList.Add(sql)
                End If
            Next
            DatabaseHelper.ExecuteSqlTran(sqlList)
            result.Result = True
            result.Message = "添加设备成功"
        Catch ex As Exception
            result.Result = False
            result.Message = "添加设备失败，失败原因：" + ex.Message
        End Try
        Return result
    End Function

    ''' <summary>
    ''' 删除工序设备数据
    ''' </summary>
    ''' <param name="deleResIds"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DeleteStepResourceData(ByVal deleResIds As String) As uMESProcessResult
        Dim result As New uMESProcessResult
        Try
            Dim sql As String = String.Format("delete SpecResourceInfo where SpecResourceInfoid in ({0})", deleResIds)
            DatabaseHelper.ExecuteSql(sql)
            result.Result = True
            result.Message = "移除设备成功"
        Catch ex As Exception
            result.Result = False
            result.Message = "移除设备失败，失败原因：" + ex.Message
        End Try
        Return result
    End Function
    ''' <summary>
    ''' 保存工序生产周期数据
    ''' </summary>
    ''' <param name="dtCycle"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveStepCycleDatas(ByVal dtCycle As DataTable) As uMESProcessResult
        Dim result As New uMESProcessResult
        Try
            Dim sqlList As New ArrayList
            For Each cyc As DataRow In dtCycle.Rows
                Dim strSql = "UPDATE Workflowstep wfs SET "
                strSql += String.Format("wfs.preparecycle = '{0}',", cyc("PREPARECYCLE").ToString())
                strSql += String.Format("wfs.makecycle = '{0}',", cyc("MAKECYCLE").ToString())
                strSql += String.Format("wfs.transfercycle = '{0}',", cyc("TRANSFERCYCLE").ToString())
                strSql += String.Format("wfs.shipcycle = '{0}',", cyc("SHIPCYCLE").ToString())
                strSql += String.Format("wfs.commicycle = '{0}',", cyc("COMMICYCLE").ToString())
                If cyc("MAKEEMPCOUNTS").ToString() = "null" Then '该字段为整型，需特殊处理
                    strSql += String.Format("wfs.makeempcounts = null,")
                Else
                    strSql += String.Format("wfs.makeempcounts = '{0}',", cyc("MAKEEMPCOUNTS").ToString())
                End If
                strSql += String.Format("wfs.chargingqty = '{0}',", cyc("CHARGINGQTY").ToString())
                strSql += String.Format("wfs.multimachineparallel = {0},", cyc("MULTIMACHINEPARALLEL").ToString())
                strSql += String.Format("wfs.notes = '{0}'", cyc("NOTES").ToString())
                strSql += String.Format(" WHERE wfs.workflowstepid='{0}' ", cyc("WORKFLOWSTEPID").ToString())
                sqlList.Add(strSql)
            Next
            DatabaseHelper.ExecuteSqlTran(sqlList)
            result.Result = True
            result.Message = "保存生产周期成功"
        Catch ex As Exception
            result.Result = False
            result.Message = "更新生产周期失败，失败原因：" + ex.Message
        End Try
        Return result
    End Function

    ''' <summary>
    ''' 保存工序工区数据
    ''' </summary>
    ''' <param name="dtWorkcenter">工区数据</param>
    ''' <param name="workflowID">工艺ID</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveStepWorkCenterDatas(ByVal dtWorkcenter As DataTable, ByVal workflowID As String) As uMESProcessResult
        Dim result As New uMESProcessResult
        Try
            Dim sqlList As New ArrayList
            Dim strsql As New StringBuilder
            Dim strValues As String = "" '工区id合集
            '更新工区
            For Each st As DataRow In dtWorkcenter.Rows
                If Not strValues.Contains(st("workCenterID").ToString) Then
                    strValues += st("workCenterID").ToString + ","
                End If
                strsql.Clear()
                strsql.AppendFormat("UPDATE Workflowstep wfs SET wfs.workcenterid='{0}',wfs.teamid='{1}' WHERE wfs.workflowstepid='{2}'",
                                     st("workCenterID"), st("teamID"), st("workflowstepid"))
                sqlList.Add(strsql.ToString)
            Next
            strValues = strValues.Trim(",")
            '更新总表批次工区
            strsql.Clear()
            strsql.AppendFormat("UPDATE containercurrentinfo cci SET cci.stepworkcenterids='{0}' WHERE CCI.Isdispatch=0 AND cci.workflowid='{1}' AND (cci.stepworkcenterids<>'{0}' or cci.stepworkcenterids is null)", strValues, workflowID)
            sqlList.Add(strsql.ToString)
            DatabaseHelper.ExecuteSqlTran(sqlList)
            result.Result = True
            result.Message = "更新工区成功"
        Catch ex As Exception
            result.Result = False
            result.Message = "更新工区失败，失败原因：" + ex.Message
        End Try
        Return result
    End Function

    ''' <summary>
    ''' 保存工序商品工时数据（热表）
    ''' </summary>
    ''' <param name="dtCommodityTime"></param>
    ''' <param name="workflowID"></param>
    ''' <param name="userInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveStepCommodityTimeDatas(ByVal dtCommodityTime As DataTable, ByVal workflowID As String, ByVal userInfo As CurrentUserInfo) As uMESProcessResult
        Dim result As New uMESProcessResult
        Try
            Dim sqlList As New ArrayList
            Dim strSql = New StringBuilder
            '查询操作的工艺是不是热表分厂的
            strSql.AppendFormat(<string>SELECT f.factoryname FROM workflow w 
                                LEFT JOIN factory f ON f.factoryid = w.factoryid
                                WHERE w.workflowid = '{0}' </string>.Value, workflowID)

            If Not DatabaseHelper.Query_ReturnDatTable(strSql.ToString()).Rows(0)("factoryname").ToString().Contains("热表") Then
                result.Result = False
                result.Message = "只能选择本分厂的工艺进行维护"
                Return result
            End If
            '更新工序的商品工时
            For Each dr As DataRow In dtCommodityTime.Rows
                strSql.Clear()
                strSql.AppendLine("UPDATE Workflowstep wfs SET ")
                strSql.AppendFormat("wfs.commoditytime = '{0}'", dr("COMMODITYTIME").ToString())
                strSql.AppendFormat(" WHERE wfs.workflowstepid='{0}' ", dr("WORKFLOWSTEPID").ToString())
                sqlList.Add(strSql)
            Next
            '全部执行
            DatabaseHelper.ExecuteSqlTran(sqlList)
            result.Result = True
            result.Message = "保存商品工时成功"
        Catch ex As Exception
            result.Result = False
            result.Message = "更新商品工时失败，失败原因：" + ex.Message
        End Try
        Return result
    End Function

End Class
